package br.com.amadeus.android.dao;

import java.util.ArrayList;
import java.util.Date;
import java.text.SimpleDateFormat;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import br.com.amadeus.android.entities.Course;
import br.com.amadeus.android.entities.CourseUser;
import br.com.amadeus.android.entities.Goal;
import br.com.amadeus.android.entities.Task;
import br.com.amadeus.android.util.Util;

public class GoalDAO {

	public static String[] columns = new String[] { GoalDAO.ID,
			GoalDAO.DEFINITION, GoalDAO.BEGINDATE, GoalDAO.ENDDATE,
			GoalDAO.AVGSTUDYTIME, GoalDAO.COURSE_USER_ID, GoalDAO.RESOLVED };

	public static final String ID = "_id";
	public static final String DEFINITION = "definition";
	public static final String BEGINDATE = "begin_date";
	public static final String ENDDATE = "end_date";
	public static final String AVGSTUDYTIME = "avg_study_time";
	public static final String COURSE_USER_ID = "course_user_id";
	public static final String RESOLVED = "resolved";

	private SQLiteDatabase db;

	private static GoalDAO instance = new GoalDAO();

	public static GoalDAO getInstance(Context context) {

		if (instance.db == null || !instance.db.isOpen()) {
			DBHelper helper = new DBHelper(context);
			try {
				instance.db = helper.getWritableDatabase();				
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return instance;
	}

	public GoalDAO() {
	}

	public long save(Goal goal) {
		long id = goal.getId();
		if (id == 0) {
			return insert(goal);
		} else {
			return update(goal);
		}
	}

	private long insert(Goal goal) {
		long id_goal = -1;

		db.beginTransaction();
		try {

			ContentValues values = new ContentValues();
			SimpleDateFormat dtFormat = new SimpleDateFormat(
					"yyyy-MM-dd HH:mm");
			values.put(GoalDAO.DEFINITION, goal.getDefinition());
			/*values.put(GoalDAO.BEGINDATE, dtFormat.format(goal.getBeginDate()));
			values.put(GoalDAO.ENDDATE, dtFormat.format(goal.getEndDate()));*/
			values.put(GoalDAO.BEGINDATE, goal.getBeginDate());
			values.put(GoalDAO.ENDDATE, goal.getEndDate());
			values.put(GoalDAO.AVGSTUDYTIME, goal.getAvgStudyTime());
			values.put(GoalDAO.COURSE_USER_ID, goal.getCourseUser().getId());
			values.put(GoalDAO.RESOLVED, Boolean.toString(goal.isResolved()));

			id_goal = db.insert(DBHelper.TABLE_GOAL, null, values);
			if (id_goal != -1)
				db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}

		return id_goal;
	}

	private long update(Goal goal) {
		boolean deuErro = false;

		db.beginTransaction();
		try {
			SimpleDateFormat dtFormat = new SimpleDateFormat(
					"yyyy-MM-dd HH:mm:ss");
			ContentValues values = new ContentValues();
			values.put(GoalDAO.DEFINITION, goal.getDefinition());
			/*values.put(GoalDAO.BEGINDATE, dtFormat.format(goal.getBeginDate()));
			values.put(GoalDAO.ENDDATE, dtFormat.format(goal.getEndDate()));*/
			values.put(GoalDAO.BEGINDATE, goal.getBeginDate());
			values.put(GoalDAO.ENDDATE, goal.getEndDate());
			values.put(GoalDAO.AVGSTUDYTIME, goal.getAvgStudyTime());
			values.put(GoalDAO.COURSE_USER_ID, goal.getCourseUser().getId());
			values.put(GoalDAO.RESOLVED, Boolean.toString(goal.isResolved()));

			deuErro = db.update(DBHelper.TABLE_GOAL, values, GoalDAO.ID
					+ " = ?", new String[] { String.valueOf(goal.getId()) }) == 0;

			if (!deuErro)
				db.setTransactionSuccessful();

		} finally {
			db.endTransaction();
		}
		return goal.getId();
	}

	public void delete(long id) {
		db.beginTransaction();
		try {
			db.delete(DBHelper.TABLE_GOAL, GoalDAO.ID + "=?",
					new String[] { String.valueOf(id) });
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	public Goal getGoal(int id) {
		Cursor c = db.query(DBHelper.TABLE_GOAL, null, GoalDAO.ID + "=" + id,
				null, null, null, null);
		Goal goal = null;
		if (c.getCount() > 0) {
			c.moveToFirst();
			goal = fillGoal(c);
		}
		c.close();
		return goal;
	}

	protected static Goal fillGoal(Cursor c) {
		int id = c.getInt(c.getColumnIndex(GoalDAO.ID));
		String definition = c.getString(c.getColumnIndex(GoalDAO.DEFINITION));
		/*Date beginDate = Util.getDate(
				c.getString(c.getColumnIndex(GoalDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);
		Date endDate = Util.getDate(
				c.getString(c.getColumnIndex(GoalDAO.ENDDATE)),
				Util.FORMATO_DATA_BD);*/
		String beginDate = c.getString(c.getColumnIndex(GoalDAO.BEGINDATE));
		String endDate = c.getString(c.getColumnIndex(GoalDAO.ENDDATE));
		int avgStudyTime = c.getInt(c.getColumnIndex(GoalDAO.AVGSTUDYTIME));
		CourseUser cu = new CourseUser(c.getInt(c
				.getColumnIndex(GoalDAO.COURSE_USER_ID)));
		boolean resolved;
		if (c.getString(c.getColumnIndex(GoalDAO.RESOLVED)).equals("TRUE")
				|| c.getString(c.getColumnIndex(GoalDAO.RESOLVED)) != "TRUE")
			resolved = true;
		else
			resolved = false;

		return new Goal(id, definition, beginDate, endDate, avgStudyTime, cu,
				resolved);
	}

	public Goal getGoalWithCouseUser(int id) {
		Cursor c = db.query(DBHelper.TABLE_GOAL, null, GoalDAO.ID + "=" + id,
				null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			return fillGoalWithCourseUser(c);
		}
		return null;
	}

	protected Goal fillGoalWithCourseUser(Cursor c) {
		int id = c.getInt(c.getColumnIndex(GoalDAO.ID));
		String definition = c.getString(c.getColumnIndex(GoalDAO.DEFINITION));
		/*Date beginDate = Util.getDate(
				c.getString(c.getColumnIndex(GoalDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);
		Date endDate = Util.getDate(
				c.getString(c.getColumnIndex(GoalDAO.ENDDATE)),
				Util.FORMATO_DATA_BD);*/
		String beginDate = c.getString(c.getColumnIndex(GoalDAO.BEGINDATE));
		String endDate = c.getString(c.getColumnIndex(GoalDAO.ENDDATE));
		int avgStudyTime = c.getInt(c.getColumnIndex(GoalDAO.AVGSTUDYTIME));
		CourseUser cu = new CourseUser(c.getInt(c
				.getColumnIndex(GoalDAO.COURSE_USER_ID)));
		boolean resolved;
		if (c.getString(c.getColumnIndex(GoalDAO.RESOLVED)).equals("TRUE")
				|| c.getString(c.getColumnIndex(GoalDAO.RESOLVED)) != "TRUE")
			resolved = true;
		else
			resolved = false;

		Goal goal = new Goal(id, definition, beginDate, endDate, avgStudyTime,
				cu, resolved);
		Cursor cc = db.query(DBHelper.TABLE_COURSEUSER, null, "_id = ?",
				new String[] { String.valueOf(goal.getCourseUser().getId()) },
				null, null, null);
		if (cc.getCount() > 0) {
			cc.moveToFirst();
			goal.setCourseUser(CourseUserDAO.fillCourseUser(cc));
		}
		return goal;
	}

	public Goal getGoalFull(int id) {
		Cursor c = db.query(DBHelper.TABLE_GOAL, null, GoalDAO.ID + "=" + id,
				null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			return fillGoalFull(c);
		}
		return null;
	}

	protected Goal fillGoalFull(Cursor c) {
		int id = c.getInt(c.getColumnIndex(GoalDAO.ID));
		String definition = c.getString(c.getColumnIndex(GoalDAO.DEFINITION));
		/*Date beginDate = Util.getDate(
				c.getString(c.getColumnIndex(GoalDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);
		Date endDate = Util.getDate(
				c.getString(c.getColumnIndex(GoalDAO.ENDDATE)),
				Util.FORMATO_DATA_BD);*/
		String beginDate = c.getString(c.getColumnIndex(GoalDAO.BEGINDATE));
		String endDate = c.getString(c.getColumnIndex(GoalDAO.ENDDATE));
		int avgStudyTime = c.getInt(c.getColumnIndex(GoalDAO.AVGSTUDYTIME));
		CourseUser cu = new CourseUser(c.getInt(c
				.getColumnIndex(GoalDAO.COURSE_USER_ID)));
		boolean resolved;
		if (c.getString(c.getColumnIndex(GoalDAO.RESOLVED)).equals("TRUE")
				|| c.getString(c.getColumnIndex(GoalDAO.RESOLVED)) != "TRUE")
			resolved = true;
		else
			resolved = false;

		Goal goal = new Goal(id, definition, beginDate, endDate, avgStudyTime,
				cu, resolved, new ArrayList<Task>());
		Cursor cc = db.query(DBHelper.TABLE_COURSEUSER, null, "_id = ?",
				new String[] { String.valueOf(goal.getCourseUser().getId()) },
				null, null, null);
		if (cc.getCount() > 0) {
			cc.moveToFirst();
			goal.setCourseUser(CourseUserDAO.fillCourseUser(cc));
		}

		Cursor tc = db
				.query(DBHelper.TABLE_TASK, null, "goal_id = ?",
						new String[] { String.valueOf(goal.getId()) }, null,
						null, null);

		tc.moveToFirst();

		while (!tc.isAfterLast()) {
			goal.getTaskList().add(TaskDAO.fillTask(tc));
			tc.moveToNext();
		}

		return goal;
	}

	public Goal getGoalWithTasks(int id) {
		Cursor c = db.query(DBHelper.TABLE_GOAL, null, GoalDAO.ID + "=" + id,
				null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			return fillGoalWithTasks(c);
		}
		return null;

	}
	
	public ArrayList<Goal> getAllGoalByCourse(int course_id){
		ArrayList<Goal> goalList = new ArrayList<Goal>();
		
		Cursor c = db.query(DBHelper.TABLE_GOAL, null, GoalDAO.COURSE_USER_ID+"="+ course_id,
				null, null, null, null);
		if (c.getCount() > 0) {
			c.moveToFirst();
			do {
				
				goalList.add(fillGoal(c));
				
			} while (c.moveToNext());
				
		}
		c.close();
		return goalList;
		
	}

	protected Goal fillGoalWithTasks(Cursor c) {
		int id = c.getInt(c.getColumnIndex(GoalDAO.ID));
		String definition = c.getString(c.getColumnIndex(GoalDAO.DEFINITION));
		/*Date beginDate = Util.getDate(
				c.getString(c.getColumnIndex(GoalDAO.BEGINDATE)),
				Util.FORMATO_DATA_BD);*/
		
		/*Date endDate = Util.getDate(
				c.getString(c.getColumnIndex(GoalDAO.ENDDATE)),
				Util.FORMATO_DATA_BD);*/
		String beginDate = c.getString(c.getColumnIndex(GoalDAO.BEGINDATE));
		String endDate = c.getString(c.getColumnIndex(GoalDAO.ENDDATE));
		int avgStudyTime = c.getInt(c.getColumnIndex(GoalDAO.AVGSTUDYTIME));
		CourseUser cu = new CourseUser(c.getInt(c
				.getColumnIndex(GoalDAO.COURSE_USER_ID)));
		boolean resolved;
		if (c.getString(c.getColumnIndex(GoalDAO.RESOLVED)).equals("TRUE")
				|| c.getString(c.getColumnIndex(GoalDAO.RESOLVED)) == "TRUE")
			resolved = true;
		else
			resolved = false;

		Goal goal = new Goal(id, definition, beginDate, endDate, avgStudyTime,
				cu, resolved);

		Cursor tc = db
				.query(DBHelper.TABLE_TASK, null, "goal_id = ?",
						new String[] { String.valueOf(goal.getId()) }, null,
						null, null);

		tc.moveToFirst();

		while (!tc.isAfterLast()) {
			goal.getTaskList().add(TaskDAO.fillTask(tc));
			tc.moveToNext();
		}

		return goal;
	}

}
